package com.zycfc.zsf.boot.mq.rabbitmq.handler;

import javax.sql.*;
import com.zycfc.zsf.boot.mq.rabbitmq.handler.domain.*;
import com.zycfc.zsf.boot.mq.rabbitmq.util.*;
import org.springframework.util.*;
import javax.annotation.*;
import com.zycfc.zsf.boot.util.text.*;
import java.util.*;
import org.slf4j.*;

public class DbProcessProvider
{
    private static final Logger log;
    public DataSource dataSource;
    private boolean initSqlFlag;
    private String profilesActive;
    private String curVersion;
    private static final int BATCH_LIMIT = 2000;
    
    public DbProcessProvider(final DataSource dataSource, final boolean initSqlFlag, final String profilesActive) {
        this.curVersion = "0.0.1";
        this.dataSource = dataSource;
        this.initSqlFlag = initSqlFlag;
        this.profilesActive = profilesActive;
    }
    
    public MqPager getProducerErrs(int pageNo, int pageSize, final String queueName, final Date tranDt, final String sts, final String msgLike) {
        DbProcessProvider.log.info("***********getProducerErrs*****");
        if (pageNo < 1) {
            pageNo = 1;
        }
        if (pageSize < 2) {
            pageSize = 2;
        }
        final int startNo = (pageNo - 1) * pageSize;
        final List<Object> params = new ArrayList<Object>();
        String sqlWhere = " WHERE 1=1";
        if (!StringUtils.isEmpty((Object)tranDt)) {
            sqlWhere += " AND tran_dt=? ";
            params.add(tranDt);
        }
        if (!StringUtils.isEmpty(sts)) {
            sqlWhere += " AND sts=? ";
            params.add(sts);
        }
        if (!StringUtils.isEmpty(queueName)) {
            sqlWhere += " AND queue_name=?";
            params.add(queueName);
        }
        if (!StringUtils.isEmpty(msgLike)) {
            sqlWhere += " AND request_data like concat('%', ?, '%')";
            params.add(msgLike);
        }
        final String sqlOrder = " order by opt_time desc ";
        final String sqlLimit = " limit " + startNo + "," + pageSize;
        final String sqlTotal = "select count(*) FROM  t_sys_mq_producer_errs  " + sqlWhere;
        final long total = DbUtils.executeQueryObject(this.dataSource, sqlTotal, params.toArray());
        final String sql = "SELECT  id,application_name AS applicationName,message_id AS messageId,queue_name AS queueName,request_data AS requestData, request_ip AS requestIp,sts,create_time AS createTime,update_time AS updateTime,tran_dt AS tranDt,opt_time AS optTime  FROM  t_sys_mq_producer_errs  " + sqlWhere + sqlOrder + sqlLimit;
        DbProcessProvider.log.info(sql);
        final List<Map<String, Object>> datas = DbUtils.executeList(this.dataSource, sql, params.toArray());
        final MqPager pager = new MqPager(total, pageNo, pageSize, datas);
        return pager;
    }
    
    public Map<String, Object> getProducerErrsById(final String id) {
        DbProcessProvider.log.info("***********getProducerErrsById*****");
        final List<Object> params = new ArrayList<Object>();
        final String sqlWhere = " WHERE id=? ";
        params.add(id);
        final String sql = "SELECT  id,application_name AS applicationName,message_id AS messageId,queue_name AS queueName,request_data AS requestData, request_ip AS requestIp,sts,create_time AS createTime,update_time AS updateTime,tran_dt AS tranDt,opt_time AS optTime  FROM  t_sys_mq_producer_errs  " + sqlWhere;
        DbProcessProvider.log.info(sql);
        final List<Map<String, Object>> datas = DbUtils.executeList(this.dataSource, sql, params.toArray());
        return CollectionUtils.isEmpty((Collection)datas) ? null : datas.get(0);
    }
    
    public List<Map<String, Object>> getProducerQueueErrs(final String queueName, int limit) {
        DbProcessProvider.log.info("***********getProducerQueueErrs*****");
        if (limit == 0) {
            limit = 2000;
        }
        final List<Object> params = new ArrayList<Object>();
        String sqlWhere = " WHERE 1=1";
        if (!StringUtils.isEmpty(queueName)) {
            sqlWhere += " AND queue_name=?";
            params.add(queueName);
        }
        sqlWhere += " AND sts!=?";
        params.add("1");
        final String sqlOrder = " order by opt_time asc ";
        final String sqlLimit = " limit " + limit;
        final String sql = "SELECT  id,application_name AS applicationName,message_id AS messageId,queue_name AS queueName,request_data AS requestData, request_ip AS requestIp,sts,create_time AS createTime,update_time AS updateTime,tran_dt AS tranDt,opt_time AS optTime  FROM  t_sys_mq_producer_errs  " + sqlWhere + sqlOrder + sqlLimit;
        DbProcessProvider.log.info(sql);
        final List<Map<String, Object>> datas = DbUtils.executeList(this.dataSource, sql, params.toArray());
        return datas;
    }
    
    public boolean updateProducerErrsById(final String id, final String sts) {
        DbProcessProvider.log.info("***********updateProducerErrsById*****");
        final List<Object> params = new ArrayList<Object>();
        params.add(sts);
        params.add(id);
        final String sql = "update t_sys_mq_producer_errs set sts = ?, update_time=now() WHERE id=? ";
        DbProcessProvider.log.info(sql);
        return DbUtils.executeUpdate(this.dataSource, sql, params.toArray());
    }
    
    public MqPager getConsumerErrs(int pageNo, int pageSize, final String queueName, final Date tranDt, final String sts, final String msgLike) {
        DbProcessProvider.log.info("***********getConsumerErrs*****");
        if (pageNo < 1) {
            pageNo = 1;
        }
        if (pageSize < 2) {
            pageSize = 2;
        }
        final int startNo = (pageNo - 1) * pageSize;
        final List<Object> params = new ArrayList<Object>();
        String sqlWhere = " WHERE 1=1";
        if (!StringUtils.isEmpty((Object)tranDt)) {
            sqlWhere += " AND tran_dt=? ";
            params.add(tranDt);
        }
        if (!StringUtils.isEmpty(sts)) {
            sqlWhere += " AND sts=? ";
            params.add(sts);
        }
        if (!StringUtils.isEmpty(queueName)) {
            sqlWhere += " AND queue_name=?";
            params.add(queueName);
        }
        if (!StringUtils.isEmpty(msgLike)) {
            sqlWhere += " AND request_data like concat('%', ?, '%')";
            params.add(msgLike);
        }
        final String sqlOrder = " order by opt_time desc ";
        final String sqlLimit = " limit " + startNo + "," + pageSize;
        final String sqlTotal = "select count(*) FROM  t_sys_mq_consumer_errs  " + sqlWhere;
        final long total = DbUtils.executeQueryObject(this.dataSource, sqlTotal, params.toArray());
        final String sql = "SELECT  id,application_name AS applicationName,message_id AS messageId,queue_name AS queueName,request_data AS requestData, request_ip AS requestIp,sts,create_time AS createTime,update_time AS updateTime,tran_dt AS tranDt, correlation_id AS correlationId,request_class AS requestClass,opt_time AS optTime  FROM  t_sys_mq_consumer_errs  " + sqlWhere + sqlOrder + sqlLimit;
        DbProcessProvider.log.info(sql);
        final List<Map<String, Object>> datas = DbUtils.executeList(this.dataSource, sql, params.toArray());
        final MqPager pager = new MqPager(total, pageNo, pageSize, datas);
        return pager;
    }
    
    public Map<String, Object> getConsumerErrsById(final String id) {
        DbProcessProvider.log.info("***********getConsumerErrsById*****");
        final List<Object> params = new ArrayList<Object>();
        final String sqlWhere = " WHERE id=? ";
        params.add(id);
        final String sql = "SELECT  id,application_name AS applicationName,message_id AS messageId,queue_name AS queueName,request_data AS requestData, request_ip AS requestIp,sts,create_time AS createTime,update_time AS updateTime,tran_dt AS tranDt, correlation_id AS correlationId,request_class AS requestClass,opt_time AS optTime FROM  t_sys_mq_consumer_errs  " + sqlWhere;
        DbProcessProvider.log.info(sql);
        final List<Map<String, Object>> datas = DbUtils.executeList(this.dataSource, sql, params.toArray());
        return CollectionUtils.isEmpty((Collection)datas) ? null : datas.get(0);
    }
    
    public List<Map<String, Object>> getConsumerQueueErrs(final String queueName, int limit) {
        DbProcessProvider.log.info("***********getConsumerQueueErrs*****");
        if (limit == 0) {
            limit = 2000;
        }
        final List<Object> params = new ArrayList<Object>();
        String sqlWhere = " WHERE 1=1";
        if (!StringUtils.isEmpty(queueName)) {
            sqlWhere += " AND queue_name=?";
            params.add(queueName);
        }
        sqlWhere += " AND sts!=?";
        params.add("1");
        final String sqlOrder = " order by opt_time asc ";
        final String sqlLimit = " limit " + limit;
        final String sql = "SELECT  id,application_name AS applicationName,message_id AS messageId,queue_name AS queueName,request_data AS requestData, request_ip AS requestIp,sts,create_time AS createTime,update_time AS updateTime,tran_dt AS tranDt, correlation_id AS correlationId,request_class AS requestClass,opt_time AS optTime FROM  t_sys_mq_consumer_errs  " + sqlWhere + sqlOrder + sqlLimit;
        DbProcessProvider.log.info(sql);
        final List<Map<String, Object>> datas = DbUtils.executeList(this.dataSource, sql, params.toArray());
        return datas;
    }
    
    public boolean updateConsumerErrsById(final String id, final String sts) {
        DbProcessProvider.log.info("***********updateConsumerErrsById*****");
        final List<Object> params = new ArrayList<Object>();
        params.add(sts);
        params.add(id);
        final String sql = "update t_sys_mq_consumer_errs set sts = ?, update_time=now() WHERE id=? ";
        DbProcessProvider.log.info(sql);
        return DbUtils.executeUpdate(this.dataSource, sql, params.toArray());
    }
    
    public void saveProducerErrLog(final TSysMqProducerErrs err) {
        final String sql = " INSERT INTO t_sys_mq_producer_errs (id,application_name,message_id,queue_name,request_data,request_ip,sts,create_time,update_time,tran_dt,opt_time,correlation_id,message_type) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        final List<Object> argsList = new ArrayList<Object>();
        argsList.add(err.getId());
        argsList.add(err.getApplicationName());
        argsList.add(err.getMessageId());
        argsList.add(err.getQueueName());
        argsList.add(err.getRequestData());
        argsList.add(err.getRequestIp());
        argsList.add(err.getSts());
        argsList.add(err.getCreateTime());
        argsList.add(err.getUpdateTime());
        argsList.add(err.getTranDt());
        argsList.add(err.getOptTime());
        argsList.add(err.getCorrelationId());
        argsList.add(err.getMessageType());
        DbUtils.executeUpdate(this.dataSource, sql, argsList.toArray());
    }
    
    public void saveConsumerErrLog(final TSysMqConsumerErrs err) {
        final String sql = " INSERT INTO t_sys_mq_consumer_errs (id,application_name,message_id,queue_name,request_data,request_ip,sts,create_time,update_time,tran_dt,correlation_id,request_class,opt_time) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        final List<Object> argsList = new ArrayList<Object>();
        argsList.add(err.getId());
        argsList.add(err.getApplicationName());
        argsList.add(err.getMessageId());
        argsList.add(err.getQueueName());
        argsList.add(err.getRequestData());
        argsList.add(err.getRequestIp());
        argsList.add(err.getSts());
        argsList.add(err.getCreateTime());
        argsList.add(err.getUpdateTime());
        argsList.add(err.getTranDt());
        argsList.add(err.getCorrelationId());
        argsList.add(err.getRequestClass());
        argsList.add(err.getOptTime());
        DbUtils.executeUpdate(this.dataSource, sql, argsList.toArray());
    }
    
    @PostConstruct
    public void initTable() {
        if ("prod".equals(this.profilesActive)) {
            this.initSqlFlag = false;
        }
        if (!this.initSqlFlag) {
            return;
        }
        final String sql = "select database()";
        final String schemaName = DbUtils.executeQuery(this.dataSource, sql);
        this.initTable(schemaName, "t_sys_mq_producer_errs");
        this.initTable(schemaName, "t_sys_mq_consumer_errs");
    }
    
    public void initTable(final String schemaName, final String tableName) {
        String sql = "SELECT  table_comment  FROM  information_schema.TABLES where table_schema='" + schemaName + "' and  table_name='" + tableName + "'";
        final String comment = DbUtils.executeQuery(this.dataSource, sql);
        final String fileNameSuffix = "init";
        if (StringUtils.isEmpty(comment)) {
            sql = getInitSQL(tableName, fileNameSuffix);
            DbUtils.executeUpdate(this.dataSource, sql, null);
            return;
        }
        final String version = comment.split("#")[1];
        if (this.curVersion.equals(version)) {
            return;
        }
        sql = "DROP TABLE IF EXISTS " + tableName;
        DbUtils.executeUpdate(this.dataSource, sql, null);
        sql = getInitSQL(tableName, fileNameSuffix);
        DbUtils.executeUpdate(this.dataSource, sql, null);
    }
    
    private static String getInitSQL(final String tableName, final String fileNameSuffix) {
        final List<String> words = new ArrayList<String>();
        final String path = "classpath:config/zsf/boot/rabbit/sql/" + tableName + "_" + fileNameSuffix + ".sql";
        ResourceLoaderUtils.load((ResourceLoader)new ResourceLoader() {
            public void clear() {
            }
            
            public void load(final List<String> lines) {
                words.addAll(lines);
            }
        }, path);
        final StringBuffer sb = new StringBuffer();
        for (final String str : words) {
            if (sb.length() > 0) {
                sb.append("\r\n");
            }
            sb.append(str);
        }
        DbProcessProvider.log.info(sb.toString());
        final String sql = sb.toString();
        return sql;
    }
    
    static {
        log = LoggerFactory.getLogger((Class)DbProcessProvider.class);
    }
}
